/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ssi.mrms.dao.Impl;

import com.ssi.mrms.dao.ComplaintDao;
import com.ssi.mrms.dao.mapping.ComplaintMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.RMcphdrModel;
import com.ssi.mrms.model.TowerModel;
import com.ssi.mrms.utils.CommonUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import static java.text.MessageFormat.format;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class ComplaintDaoImpl implements ComplaintDao {
    
    private static final Logger logger = Logger.getLogger(ComplaintDaoImpl.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final String SQL_QUERY = "SELECT * FROM rmcphdr a LEFT JOIN rmcpdtl b ON b.CPHID = a.CPHID LEFT JOIN rmrmlst room ON room.RLRMID = a.RLRMID WHERE 1=1  ";
    private static final String SQL_QUERY_RMCPHDR_ALL = "SELECT * FROM rmcphdr ";
    private static final String SQL_QUERY_RMCPHDR_BY_ID = "SELECT cp.*, rm.* FROM rmcphdr cp INNER JOIN rmrmlst rm ON cp.rlrmid = rm.rlrmid where cphid = ? ";

    
    @Override
    public List<RMcphdrModel> findAll() {

        List<RMcphdrModel> list = jdbcTemplate.query(SQL_QUERY_RMCPHDR_ALL, new Object[]{}, new ComplaintMapping());

        return list == null ? new ArrayList<RMcphdrModel>() : list;
    }

    @Override
    public RMcphdrModel findById(Integer id) {
        List<RMcphdrModel> model = jdbcTemplate.query(SQL_QUERY_RMCPHDR_BY_ID, new Object[]{id}, new ComplaintMapping());

        return model.size() <= 0 ? new RMcphdrModel() : model.get(0);
    }

    private static final String SQL_INSERT_RMCPHDR = "INSERT INTO rmcphdr (cphid,cphdocno,rlrmid,cphdocstat,cphreqby,cphreqdat,cphreqtel,cphreqemail,cphrcvby,cphrcvdat,cphcreby,cphcredat,cphupdby,cphupddat) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    @Override
    public void create(final RMcphdrModel model) throws Exception {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMCPHDR), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setInt(++i, model.getCphid());
                ps.setString(++i, model.getCphdocno() == null ? null : model.getCphdocno());
                ps.setInt(++i, model.getRlrmid());
                ps.setString(++i, model.getCphdocstat() == null ? null : model.getCphdocstat());
                ps.setString(++i, model.getCphreqby() == null ? null : model.getCphreqby());
                ps.setDate(++i, model.getCphcredat() == null ? null : new java.sql.Date(model.getCphcredat().getTime()));
                ps.setString(++i, model.getCphreqtel() == null ? null : model.getCphreqtel());
                ps.setString(++i, model.getCphreqemail() == null ? null : model.getCphreqemail());
                ps.setString(++i, model.getCphrcvby() == null ? null : model.getCphrcvby());
                ps.setDate(++i, model.getCphrcvdat() == null ? null : new java.sql.Date(model.getCphrcvdat().getTime()));
                ps.setString(++i, model.getCphcreby() == null ? null : model.getCphcreby());
                ps.setDate(++i, model.getCphcredat() == null ? null : new java.sql.Date(model.getCphcredat().getTime()));
                ps.setString(++i, model.getCphupdby() == null ? null : model.getCphupdby());
                ps.setDate(++i, model.getCphupddat() == null ? null : new java.sql.Date(model.getCphupddat().getTime()));
                return ps;
            }

        }, keyHolder);
    }

    private static final String SQL_UPDATE_RMCPHDR = "UPDATE rmcphdr SET cphdocno=?,rlrmid=?,cphdocstat=?,cphreqby=?,cphreqdat=?,cphreqtel=?,cphreqemail=?,cphrcvby=?,cphrcvdat=?,cphcreby=?,cphcredat=?,cphupdby=?,cphupddat=? WHERE cphid=?";
                         
    @Override
    public boolean update(RMcphdrModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMCPHDR),
                new Object[]{
                    model.getCphdocno(),
                    model.getRlrmid(),
                    model.getCphdocstat(),
                    model.getCphreqby(),
                    model.getCphcredat(),
                    model.getCphreqtel(),
                    model.getCphreqemail(),
                    model.getCphrcvby(),
                    model.getCphrcvdat(),
                    model.getCphcreby(),
                    model.getCphcredat(),
                    model.getCphupdby(),
                    model.getCphupddat(),
                    model.getCphid()
                });
        return row > 0;
    }

    private static final String SQL_DELETE_RMCPHDR = "DELETE FROM rmcphdr WHERE cphid=?";

    @Override
    public boolean delete(RMcphdrModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMCPHDR), new Object[]{model.getCphid()});
        return row > 0;
    }

    
    @Override
    public DataTableModel<RMcphdrModel> findDataTable(RMcphdrModel model, int iDisplayStart, int iDisplayLength) {

    	DataTableModel<RMcphdrModel> dataTableModel = new DataTableModel<RMcphdrModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{iDisplayStart, iDisplayLength});
        
        TowerModel tower = model.getTower();
        
        /* --- Where Condition  ---*/
        String sql_where = ""; 
        String sql_join = " LEFT JOIN RMRMLST TW ON TW.RLRMID = ";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND TW.RLSTTOWER = '" + tower.getTower() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND TW.RLSTFLOOR = '" + tower.getFloor() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND TW.RLSTUNITNO = '" + tower.getUnitNo() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND TW.RLSTADDR = '" + tower.getAddress() + "' ");
        }
        
         /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getCphdocno())) {
        	sql_where = sql_where.concat(" AND A.CPHDOCNO LIKE '%" + model.getCphdocno() + "%' ");
        }
        //TODO convert date
        if (!CommonUtils.isNullOrEmtry(model.getCphdocstat())) {
        	sql_where = sql_where.concat(" AND A.CSDRCVDAT LIKE '%" + model.getCphdocstat() + "%' ");
        }
        //TODO where by date condition
        if (!CommonUtils.isNullOrEmtry(model.getCphrcvdat())) {
        	sql_where = sql_where.concat(" AND A.CSDSTAT LIKE '%" + model.getCphrcvdat() + "%' ");
        }
        //TODO require to
        if (StringUtils.isNotBlank(model.getCphreqby())) {
            sql_where = sql_where.concat(" AND A.CSDTYP = '" + model.getCphreqby() + "' ");
        }
        
        /*--- Initial Query ---*/
		String sqlQuery = " SELECT A.* , TW.* FROM rmcphdr A " + sql_join +" A.RLRMID " +
				" WHERE 1=1 " + sql_where + sqlLimit;
		String sqlCount = "SELECT COUNT(1) FROM rmcphdr A " + sql_join +" A.RLRMID " +
				" WHERE 1=1"  + sql_where;
		
		// --- #1 Get data for datatable by limit row
		List<RMcphdrModel> list = jdbcTemplate.query(sqlQuery, new ComplaintMapping());
		
		// --- #2 Get row number
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(iDisplayStart);
        dataTableModel.setLength(iDisplayLength);
        
        logger.debug("SQL : " + sqlQuery);
		return dataTableModel;
    }
    
}
